package com.github.ilioili.justdoit.util.sql;

import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseErrorHandler;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;

import com.github.ilioili.justdoit.common.config.EventConstants;
import com.github.ilioili.justdoit.model.pojo.Category;
import com.github.ilioili.justdoit.model.pojo.EventRecord;
import com.github.ilioili.justdoit.model.pojo.ScheduleEvent;
import com.taihe.template.base.util.FileUtil;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;


public class DbUtil extends SQLiteOpenHelper {
    private final static String DBNAME = "DB1.db";
    private final static int VERSION = 8;
    final private static String[] NULL_WHERE_PARAMS = new String[]{};
    private static SQLiteDatabase db = null;
    private static Class[] classes = new Class[]{ScheduleEvent.class, EventRecord.class, Category.class};

    public static void backupToSdcard() {
        FileUtil.copy(DbUtil.getDb().getPath(), Environment.getExternalStorageDirectory() + "/JustDoIt.db");
    }

    public static void restoreFromSdcard() {
        try {
            SQLiteDatabase backupDb = SQLiteDatabase.openDatabase(Environment.getExternalStorageDirectory() + "/JustDoIt.db", null, SQLiteDatabase.OPEN_READONLY, new DatabaseErrorHandler() {
                @Override
                public void onCorruption(SQLiteDatabase dbObj) {

                }
            });
            SqlUtil.clearTableColumnCache();
            Cursor cursor = backupDb.rawQuery("SELECT * FROM " + getTableName(ScheduleEvent.class), null);
            List<ScheduleEvent> scheduleEvents = getItemList(ScheduleEvent.class, cursor);
            replaceList(scheduleEvents);
            cursor = backupDb.rawQuery("SELECT * FROM " + getTableName(EventRecord.class), null);
            List<EventRecord> eventRecords = getItemList(EventRecord.class, cursor);
            replaceList(eventRecords);
            SqlUtil.clearTableColumnCache();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private DbUtil(Context context) {
        super(context, DBNAME, null, VERSION);
    }

    public static final void clearTable(Class<?> c) {
        try {
            db.execSQL("delete from " + SqlUtil.getTableName(c));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void createTable(Class c) {
        db.execSQL(SqlUtil.getSqlForCreateTable(c));
    }

    public static SQLiteDatabase getDb() {
        return db;
    }

    /**
     * @param primaryKey
     * @param c
     * @param <T>
     * @return
     */
    public static final <T> T getItem(Object primaryKey, Class<T> c) {
        Cursor cursor = db.rawQuery(SqlUtil.getSqlForSelectByPrimaryKey(primaryKey, c), NULL_WHERE_PARAMS);
        return getItem(cursor, c);
    }

    public static final int getCount(Class c, String whereCondition) {
        Cursor cursor = db.rawQuery(SqlUtil.getSqlForSelectWithCondition(c, whereCondition), null);
        int count = cursor.getCount();
        cursor.close();
        return count;

    }

    /**
     * @param cursor
     * @param c
     */
    @SuppressWarnings("unchecked")
    public static <T> T getItem(Cursor cursor, Class<T> c) {
        T t = null;
        if (cursor.moveToFirst()) {
            try {
                t = (T) SqlUtil.getInstance(cursor, c);
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InstantiationException e) {
                e.printStackTrace();
            }
        }
        return t;
    }

    public static <T> ArrayList<T> getList(Class<T> c) {
        return getList(c, 0);
    }


    /**
     * @param limit limit<=0表示小于不限制
     * @return list如果为空可能是数据库没数据，也可能是表不存在
     */
    public static <T> ArrayList<T> getList(Class<T> c, int limit) {
        String sql = null;
        if (limit <= 0) {
            sql = "select * from " + SqlUtil.getTableName(c);
        } else {
            sql = "select * from " + SqlUtil.getTableName(c) + " limit " + limit;
        }
        try {
            Cursor cursor = db.rawQuery(sql, NULL_WHERE_PARAMS);
            return getItemList(c, cursor);
        } catch (Exception e) {
            e.printStackTrace();
            return new ArrayList<T>();
        }
    }

    public static final <T> ArrayList<T> getItemList(Class<T> c, Cursor cursor) {
        ArrayList<T> list = new ArrayList<T>();
        try {
            while (cursor.moveToNext()) {
                T t = (T) SqlUtil.getInstance(cursor, c);
                if (null != t) {
                    BigDecimal d;
                    list.add(t);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        cursor.close();
        return list;
    }

    public static final String getTableName(Class<?> c) {
        return SqlUtil.getTableName(c);
    }

    public static void init(Context c) {
        if (db == null) {
            db = new DbUtil(c).getWritableDatabase();
        }
        for (Class clazz : classes) {
            createTable(clazz);
        }
    }

    /**
     * @param obj
     */
    public static final void insert(Object obj) {
        db.execSQL(SqlUtil.getSqlForInsert(obj));
    }

    public static final void delete(Object obj) {
        try {
            db.execSQL(SqlUtil.getSqlForDelete(obj));
        } catch (SqlException e) {
            e.printStackTrace();
        }
    }

    /**
     * @param objs
     */
    public static final boolean insertList(List<?> objs) {
        boolean result = false;
        if (objs.isEmpty()) {
            result = true;
        } else {
            try {
                db.beginTransaction();
                for (Object obj : objs) {
                    db.execSQL(SqlUtil.getSqlForInsert(obj));
                }
                db.setTransactionSuccessful();
                result = true;
            } catch (Exception e) {
                result = false;
            } catch (Error e) {
                result = false;
            } finally {
                db.endTransaction();
            }
        }
        return result;
    }

    /**
     * @param obj
     */
    public static final void replace(Object obj) {

        try {
            db.execSQL(SqlUtil.getSqlForReplace(obj));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * @param objs
     */
    public static final boolean replaceList(List<?> objs) {
        boolean result = false;
        if (objs.isEmpty()) {
            result = true;
        } else {
            try {
                db.beginTransaction();
                for (Object obj : objs) {
                    db.execSQL(SqlUtil.getSqlForReplace(obj));
                }
                db.setTransactionSuccessful();
                result = true;
            } catch (Exception e) {
                result = false;
            } catch (Error e) {
                result = false;
            } finally {
                db.endTransaction();
            }
        }
        return result;
    }

    /**
     * @param obj 必须定义了主键，参考SqlField.class
     */
    public static final void update(Object obj) {
        if (null == obj) {
            return;
        }
        db.execSQL(SqlUtil.getSqlForUpdate(obj));
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        for (Class clazz : classes) {
            Cursor cursor = db.rawQuery("select sql from sqlite_master where name = ?", new String[]{getTableName(clazz)});
            if (cursor.moveToNext()) {
                String previousSql = cursor.getString(0);
                Field[] fields = clazz.getDeclaredFields();
                for (Field field : fields) {
                    if (!previousSql.contains("_" + field.getName())) {
                        db.execSQL(SqlUtil.getSqlForAddColumn(clazz, field));
                    }
                }
            }
        }
        if (oldVersion == 3) {
            db.execSQL("UPDATE " + SqlUtil.getTableName(ScheduleEvent.class) + " SET _type = " + EventConstants.TYPE_HABIT + " WHERE _isRepeat = 'true' and _type = 0");
        }
        if (oldVersion < 7) {
            db.execSQL("UPDATE " + SqlUtil.getTableName(ScheduleEvent.class) + " SET _categoryId = '" + EventConstants.DEFAULT_CATEGORY + '\'');
        }
    }


    public static void clear() {
        try {
            db.beginTransaction();
            for (Class clazz : classes) {
                db.execSQL(SqlUtil.getSqlForClearTable(clazz));
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.endTransaction();
        }

    }
}
